﻿using LFramework.Data;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DataServices
{
    public class DaoNews
    {
        IDBHelper dbhelper = DBFactory.CreateFromConfig("conn");

        public int LoadNewsCount()
        {
            string sql = "select count(*) as iCount from tbNews";

            return Convert.ToInt32(dbhelper.ExecuteScalar(sql));
        }

        public IList<NewsInfo> LoadNewsByPage(int page, int pageSize)
        {
            IList<NewsInfo> list = new List<NewsInfo>();

            string sql = null;
            if (page != 1)
            {
                int aNum = page * pageSize;

                int recBegin = pageSize * (page - 1);

                sql = @"
                           
select top " + pageSize + @" NewsID ,Title ,AddTime 
from  (select NewsID ,Title ,AddTime from tbNews 
where NewsID not in(select top " + recBegin + @" NewsID from( select NewsID from tbNews order by AddTime desc))  
order by AddTime desc) as tabresult
    
                          ";
            }
            else
            {
                sql = "select top " + page * pageSize + @"  NewsID ,Title ,AddTime from(select NewsID ,Title ,AddTime from tbNews order by AddTime desc) as tabresult";
            }

            DataTable dt = dbhelper.QueryForDataTable(sql);

            foreach (DataRow dr in dt.Rows)
            {
                NewsInfo news = new NewsInfo()
                {
                    NewsID = dr["NewsID"].ToString(),
                    Title = dr["Title"].ToString(),
                    AddTime = dr["AddTime"].ToString()
                };

                list.Add(news);
            }

            return list;
        }

        public IList<NewsInfo> LoadNewsByPage(string group, int page, int pageSize)
        {
            IList<NewsInfo> list = new List<NewsInfo>();

            string sql = null;
            if (page != 1)
            {
                int aNum = page * pageSize;

                int recBegin = pageSize * (page - 1);

                sql = @"
                           
select top " + pageSize + @" NewsID ,Title ,AddTime 
from (select NewsID ,Title ,AddTime from tbNews 
where NewsGroupName = '" + group + @"' and NewsID not in(select top " + recBegin + @" NewsID from (select NewsID from tbNews  where NewsGroupName = '" + group + @"' order by AddTime desc) as tabresult)  
order by AddTime desc) as tabresult
    
                          ";
            }
            else
            {
                sql = "select top " + page * pageSize + @"  NewsID ,Title ,AddTime from (select NewsID ,Title ,AddTime from tbNews  where NewsGroupName = '" + group + @"' order by AddTime desc) as tabresult";
            }

            DataTable dt = dbhelper.QueryForDataTable(sql);

            foreach (DataRow dr in dt.Rows)
            {
                NewsInfo news = new NewsInfo()
                {
                    NewsID = dr["NewsID"].ToString(),
                    Title = dr["Title"].ToString(),
                    AddTime = dr["AddTime"].ToString()
                };

                list.Add(news);
            }

            return list;
        }



        public NewsInfo LoadNewsById(string id)
        {
            string sql = "select * from tbNews where NewsID = '" + id + "'";

            DataRow dr = dbhelper.QueryForDataTable(sql).Rows[0];

            NewsInfo news = new NewsInfo()
                   {
                       NewsID = dr["NewsID"].ToString(),
                       Creater = dr["Creater"].ToString(),
                       IsTop = Convert.ToBoolean(dr["IsTop"]),
                       Title = dr["Title"].ToString(),
                       Modifer = dr["Modifier"].ToString(),
                       ModifyTime = dr["ModifyTime"].ToString(),
                       NewsContent = dr["NewsContent"].ToString(),
                       AddTime = dr["AddTime"].ToString(),
                       ViewCount = Convert.ToInt32(dr["ViewCount"]),
                       NewsGroupName = dr["NewsGroupName"].ToString()
                   };

            DataTable dtAtta = dbhelper.QueryForDataTable("select * from tbNewsAttachment where NewsId = '" + id + "'");

            foreach (DataRow drAtta in dtAtta.Rows)
            {
                AttachmentInfo attachment = new AttachmentInfo();
                attachment.FileName = drAtta["FileName"].ToString();
                attachment.SaveName = drAtta["SaveName"].ToString();
                news.AttachmentList.Add(attachment);
            }

            return news;
        }

        public void AddNews(NewsInfo news)
        {
            string sql = @"insert into tbNews(
                            [NewsId]
                           ,[Title]
                           ,[NewsContent]
                           ,[AddTime]
                           ,[Creater]
                           ,[IsTop] 
                           ,[ViewCount] 
                           ,[NewsGroupName]) 
                           values (
                            @NewsId,
                            @Title,
                            @NewsContent,
                            @AddTime,
                            @Creater,
                            @IsTop, 
                            @ViewCount,
                            @NewsGroupName
                            )  
                           ";

            OleDbParameter[] paras = { new OleDbParameter("NewsId",news.NewsID),
                                     new OleDbParameter("Title", news.Title) ,
                                     new OleDbParameter("NewsContent", news.NewsContent),
                                     new OleDbParameter("AddTime", DateTime.Now.ToString("yyyy-MM-dd")),
                                     new OleDbParameter("Creater", news.Creater),
                                     new OleDbParameter("IsTop", Convert.ToInt16(news.IsTop)), 
                                     new OleDbParameter("ViewCount", "1"),
                                     new OleDbParameter("NewsGroupName",news.NewsGroupName)
                                   };

            dbhelper.ExecuteSQL_InTrans(sql, paras);


            //保存附件
            foreach (var item in news.AttachmentList)
            {
                sql = "insert into tbNewsAttachment(NewsID ,FileName ,SaveName) values('" + news.NewsID + "','" + item.FileName + "','" + item.SaveName + "')";
                dbhelper.ExecuteSQL(sql);
            }
        }

        public void DeleteNews(string id)
        {
            string sql = "delete from tbNewsAttachment where NewsId = '" + id + "'";
            dbhelper.ExecuteSQL(sql);

            sql = "delete from tbNews where NewsId = '" + id + "'";
            dbhelper.ExecuteSQL(sql);
        }

        public void UpdateNews(NewsInfo news)
        {
            string sql = @"
update tbNews 
set Title = @Title , NewsGroupName = @NewsGroupName, NewsContent = @Content ,Modifier = @Modifer ,
    ModifyTime = @ModifyTime ,IsTop = @IsTop
where NewsId = '" + news.NewsID + "'";

            OleDbParameter[] paras = { new OleDbParameter("Title", news.Title) , 
                                       new OleDbParameter("NewsGroupName",news.NewsGroupName),
                                       new OleDbParameter("Content", news.NewsContent) ,
                                       new OleDbParameter("Modifer",news.Modifer),
                                       new OleDbParameter("ModifyTime",DateTime.Now.ToString("yyyyMMddHHmmss")),
                                       new OleDbParameter("IsTop",Convert.ToInt16(news.IsTop)),
                                   };

            dbhelper.ExecuteSQL(sql, paras);

            //更新附件列表
            sql = "delete from tbNewsAttachment where NewsId = '" + news.NewsID + "'";
            dbhelper.ExecuteSQL(sql);

            foreach (var item in news.AttachmentList)
            {
                sql = "insert into tbNewsAttachment(NewsID ,FileName ,SaveName) values('" + news.NewsID + "','" + item.FileName + "','" + item.SaveName + "')";
                dbhelper.ExecuteSQL(sql);
            }
        }


        public List<AttachmentInfo> LoadPictureNews()
        {
            //select top 4 tbNewsAttachment.* from tbNewsAttachment left join tbNews on tbNewsAttachment.NewsID=tbNews.NewsID where SaveName like '*.gif' order by tbNews.AddTime desc
            // left join tbNews on tbNewsAttachment.NewsID=tbNews.NewsID where (SaveName like '*.gif' or SaveName like '*.jpg' or SaveName like '*.png') and tbNews.NewsID<>''  order by tbNews.AddTime desc
            string sql = "select top 3 tbNewsAttachment.* from tbNewsAttachment where tbNewsAttachment.NewsID in (select NewsID from tbNews order by AddTime desc)";

            DataTable dtAtta = dbhelper.QueryForDataTable(sql);

            List<AttachmentInfo> AttachmentList = new List<AttachmentInfo>();

            foreach (DataRow drAtta in dtAtta.Rows)
            {
                AttachmentInfo attachment = new AttachmentInfo();
                attachment.NewsID = drAtta["NewsID"].ToString();
                attachment.FileName = drAtta["FileName"].ToString();
                attachment.SaveName = drAtta["SaveName"].ToString();
                AttachmentList.Add(attachment);
            }

            return AttachmentList;
        }
    }
}
